/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package processor;

import java.io.File;
import java.util.ArrayList;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

/**
 * Class parse a text string correspond to a sql statement
 */
public class SqlParser {

    private String input;
    private String[] operator = {" and ", " or ", " in ", " exists ",
        " not in ", " not exists "};
    private String[] syntax = {"=", "<>", ">", "<", "<=", ">=", "!=", "!>", "!<"};

    public SqlParser() {
        input = null;
    }

    public SqlParser(String input) {
        KeyWord kw = new KeyWord(input);
        this.input = kw.changKeyWordToLowerCase();
    }

    /**
     * Transform an input string to XML format to send to server
     *
     * @return XML Document
     */
    public Document parse() {
        Document doc = null;
        try {

            DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
            DocumentBuilder docBuilder = docFactory.newDocumentBuilder();

            // root elements
            doc = docBuilder.newDocument();
            Element rootElement = doc.createElement("SqlStatement");
            doc.appendChild(rootElement);

            // check statement
            DetectQuery dq = new DetectQuery(this.input);
            while (!dq.getQuery().equals("")) {
                dq.getASingleQuery();
                int s = dq.detect();
                switch (s) {
                    case Constant.SELECT:
                        doc = transformSelect(doc, dq.getSingleQuery().trim());
                        break;
                    case Constant.INSERT:
                        doc = transformInsert(doc, dq.getSingleQuery().trim());
                        break;
                    case Constant.UPDATE:
                        doc = transformUpdate(doc, dq.getSingleQuery().trim());
                        break;
                    case Constant.DELETE:
                        doc = transformDelete(doc, dq.getSingleQuery().trim());
                        break;
                    case Constant.ERROR:
                        break;
                }
            }
        } catch (ParserConfigurationException pce) {
            JOptionPane.showMessageDialog(null, pce);
        }

        return doc;
    }

    public ArrayList<Node> GetElementByTagName(String name, Node root) {
        ArrayList<Node> result = new ArrayList<Node>();

        for (int i = 0; i < root.getChildNodes().getLength(); i++) {
            if (root.getChildNodes().item(i).getNodeName().equals(name)) {
                result.add(root.getChildNodes().item(i));
            }
        }
        if (result.isEmpty()) {
            result.add(null);
        }
        return result;
    }

    /**
     * Transform XML document received from server to table
     *
     * @param doc
     */
    public JTable parseInverse(Document doc) {
        JTable r = null;
        Element root = doc.getDocumentElement();
        NodeList l = root.getChildNodes();
        for (int k = 0; k < l.getLength(); k++) {
            //check error received
            if (l.item(k).getNodeName().equals("Error")) {
                JOptionPane.showMessageDialog(null, "Error - At query " + (k + 1) + ": "
                        + l.item(k).getAttributes().item(0).getNodeValue());
                continue;
            }
            //check success received
            if (l.item(k).getNodeName().equals("Success")) {
                JOptionPane.showMessageDialog(null, "Success - At query " + (k + 1) + ": "
                        + l.item(k).getAttributes().item(0).getNodeValue());
                continue;
            }

            //get table received
            if (l.item(k).getNodeName().equals("Table")) {
                r = new JTable();

                ArrayList<Node> listCol = this.GetElementByTagName("Column", l.item(k));
                ArrayList<String> colName = new ArrayList<String>();
                for (int i = 0; i < listCol.size(); i++) {
                    colName.add(listCol.get(i).getAttributes().getNamedItem("name").getNodeValue());
                }

                DefaultTableModel model = new DefaultTableModel(null, colName.toArray()) {

                    @Override
                    public boolean isCellEditable(int row, int col) {
                        return false;
                    }
                };
                int nRow = this.GetElementByTagName("row", listCol.get(0)).size();
                for (int i = 0; i < nRow; i++) {
                    ArrayList<String> data = new ArrayList<String>();
                    for (int j = 0; j < listCol.size(); j++) {
                        data.add(this.GetElementByTagName("row",
                                listCol.get(j)).get(i).getAttributes().getNamedItem("value").getNodeValue());
                    }
                    model.addRow(data.toArray());
                }
                r.setModel(model);
                JOptionPane.showMessageDialog(null, "Success - At query " + (k + 1) + ": Table returned.");
            }
        }

        return r;
    }

    /**
     * Parse select level 1 query into xml format
     *
     * @param d
     * @param sq
     * @return
     */
    private Document transformSelect(Document d, String sq) {
        String[] sfw = sq.split("\n");
        Element query = d.createElement("Query");
        query.setAttribute("name", "Select");
        //get table
        sfw[1] = sfw[1].substring("from".length(), sfw[1].length()).trim();
        String[] fromList = sfw[1].split(",");
        for (int i = 0; i < fromList.length; i++) {
            Element table = d.createElement("From");
            String[] temp = fromList[i].trim().split(" ");//except alias name
            table.setAttribute("tabName", temp[0].trim());
            table.setAttribute("type", "none");

            query.appendChild(table);
        }
        //get column
        sfw[0] = sfw[0].substring("select".length(), sfw[0].length()).trim();
        String[] selList = sfw[0].split(",");
        for (int i = 0; i < selList.length; i++) {
            Element column = d.createElement("Column");
            column.setAttribute("name", selList[i].trim());
            column.setAttribute("as", "");
            column.setAttribute("syntax", "");

            query.appendChild(column);
        }
        //get conditions
        if (sfw.length == 3) {
            sfw[2] = sfw[2].substring("where".length(), sfw[2].length()).trim();
            ArrayList a = new ArrayList(this.detectCondition(sfw[2], 0));

            Element where = this.buildWhereElement(d, a);
            query.appendChild(where);
        }

        d.getDocumentElement().appendChild(query);

        return d;
    }

    /**
     * Parse insert query into xml format
     *
     * @param d
     * @param sq
     * @return
     */
    private Document transformInsert(Document d, String sq) {
        String[] iiv = sq.trim().split("\n");
        Element query = d.createElement("Query");
        query.setAttribute("name", "Insert");
        //get table and list column
        iiv[0] = iiv[0].trim().substring("insert into".length(), iiv[0].length()).trim();
        ////get table
        String[] tab_col = iiv[0].split("\\(");
        Element table = d.createElement("Table");
        table.setAttribute("name", tab_col[0].trim());
        query.appendChild(table);
        ////get list column
        String[] col;
        if (tab_col.length == 2) {
            query.setAttribute("isHaveColumn", "Yes");
            //sub "(" and ")"
            tab_col[1] = tab_col[1].trim().substring(0, tab_col[1].length() - 1);
            //split column
            col = tab_col[1].split(",");
        } ////there is no column --> insert type 1
        else {
            query.setAttribute("isHaveColumn", "No");
            col = null;
        }

        //get values
        iiv[1] = iiv[1].substring("values(".length(), iiv[1].length() - 1);
        String[] values = iiv[1].split(",");
        for (int i = 0; i < values.length; i++) {
            Element column = d.createElement("Column");
            String nameCol = (col == null) ? "" : col[i].trim();
            column.setAttribute("name", nameCol);
            if (isChain(values[i].trim())) {
                values[i] = values[i].trim();
                values[i] = values[i].substring(1, values[i].length() - 1);
            }
            column.setAttribute("value", values[i].trim());

            query.appendChild(column);
        }

        d.getDocumentElement().appendChild(query);

        return d;
    }

    /**
     * Parse update query into xml format
     *
     * @param d
     * @param sq
     * @return
     */
    private Document transformUpdate(Document d, String sq) {
        String[] usw = sq.split("\n");
        Element query = d.createElement("Query");
        query.setAttribute("name", "Update");
        //get table
        usw[0] = usw[0].substring("update".length(), usw[0].length()).trim();
        Element table = d.createElement("Table");
        table.setAttribute("name", usw[0]);
        query.appendChild(table);
        //get list Set
        usw[1] = usw[1].substring("set".length(), usw[1].length()).trim();
        String[] set = usw[1].split(",");
        for (int i = 0; i < set.length; i++) {
            Element se = d.createElement("Set");
            String[] s = set[i].trim().split("=");
            s[0] = s[0].trim();
            s[1] = s[1].trim();
            if (this.isChain(s[0])) {
                s[0] = s[0].substring(1, s[0].length() - 1);
            }
            se.setAttribute("name", s[0]);
            if (this.isChain(s[1])) {
                s[1] = s[1].substring(1, s[1].length() - 1);
            }
            se.setAttribute("value", s[1]);

            query.appendChild(se);
        }
        //get condition
        if (usw.length == 3) {
            usw[2] = usw[2].substring("where".length(), usw[2].length()).trim();
            ArrayList a = this.detectCondition(usw[2], 0);

            Element where = this.buildWhereElement(d, a);
            query.appendChild(where);
        }

        d.getDocumentElement().appendChild(query);
        return d;
    }

    /**
     * Parse delete query into xml format
     *
     * @param d
     * @param sq
     * @return
     */
    private Document transformDelete(Document d, String sq) {
        String[] dfw = sq.trim().split("\n");
        Element query = d.createElement("Query");
        query.setAttribute("name", "Delete");
        //get table
        dfw[0] = dfw[0].substring("delete from".length(), dfw[0].length()).trim();
        Element table = d.createElement("Table");
        table.setAttribute("name", dfw[0]);
        query.appendChild(table);
        //get condition
        if (dfw.length == 2) {
            dfw[1] = dfw[1].substring("where".length(), dfw[1].length()).trim();
            ArrayList a = this.detectCondition(dfw[1], 0);

            Element where = this.buildWhereElement(d, a);
            query.appendChild(where);
        }

        d.getDocumentElement().appendChild(query);
        return d;
    }

    /**
     * Build where element
     *
     * @param d Document
     * @param a an array list of conditions
     * @return
     */
    private Element buildWhereElement(Document d, ArrayList a) {
        Element where = d.createElement("Where");
        ArrayList a1 = this.splitCondition((String) a.get(0));

        if (a.size() == 1) {
            where.setAttribute("logic", (String) a1.get(1));
            where.setAttribute("type", (String) a1.get(3));
            Element e = d.createElement("Left");
            e.setAttribute("value", (String) a1.get(0));
            e.setAttribute("reference", "");
            where.appendChild(e);
            e = d.createElement("Right");
            e.setAttribute("value", (String) a1.get(2));
            e.setAttribute("reference", "");
            where.appendChild(e);
        } else {
//            String logic = (String) a1.get(4);
//            where.setAttribute("logic", logic);
//            where.setAttribute("type",
//                    ("and".equals(logic) || "or".equals(logic)) ? "none" : (String) a1.get(3));
            where = this.buildConditionTree(d, a, where);
        }

        return where;
    }

    /**
     * Build condition tree
     *
     * @param d
     * @param a
     * @param par
     * @param a1
     * @return
     */
    private Element buildConditionTree(Document d, ArrayList a, Element par) {
        //condition 2
        ArrayList a0 = this.splitCondition((String) a.get(0));
        String logic = (String) a0.get(4);
        par.setAttribute("logic", logic);
        par.setAttribute("type",
                ("and".equals(logic) || "or".equals(logic)) ? "none" : (String) a0.get(3));

        if (a.size() == 2) {
            //condition 1
            ArrayList a1 = this.splitCondition((String) a.get(1));
            Element cond1 = d.createElement("Condition");
            cond1.setAttribute("logic", (String) a1.get(1));
            cond1.setAttribute("type", (String) a1.get(3));

            Element e1 = d.createElement("Left");
            e1.setAttribute("value", (String) a1.get(0));
            e1.setAttribute("reference", "");
            cond1.appendChild(e1);
            e1 = d.createElement("Right");
            e1.setAttribute("value", (String) a1.get(2));
            e1.setAttribute("reference", "");
            cond1.appendChild(e1);

            par.appendChild(cond1);
            //condition 2
            Element cond2 = d.createElement("Condition");
            cond2.setAttribute("logic", (String) a0.get(1));
            cond2.setAttribute("type", (String) a0.get(3));
            e1 = d.createElement("Left");
            e1.setAttribute("value", (String) a0.get(0));
            e1.setAttribute("reference", "");
            cond2.appendChild(e1);
            e1 = d.createElement("Right");
            e1.setAttribute("value", (String) a0.get(2));
            e1.setAttribute("reference", "");
            cond2.appendChild(e1);

            par.appendChild(cond2);

            return par;
        }

        Element cond1 = d.createElement("Condition");
        cond1.setAttribute("logic", (String) a0.get(1));
        cond1.setAttribute("type", (String) a0.get(3));
        Element e1 = d.createElement("Left");
        e1.setAttribute("value", (String) a0.get(0));
        e1.setAttribute("reference", "");
        cond1.appendChild(e1);
        e1 = d.createElement("Right");
        e1.setAttribute("value", (String) a0.get(2));
        e1.setAttribute("reference", "");
        cond1.appendChild(e1);

        par.appendChild(cond1);
        a.remove(0);
        Element cond2 = d.createElement("Condition");
        cond2 = this.buildConditionTree(d, a, cond2);
        par.appendChild(cond2);

        return par;
    }

    /**
     * Split a single condition into the parts : left, right operand, syntax,
     * type of condition, and next operator
     *
     * @param cond
     * @return an array of those parts
     */
    private ArrayList splitCondition(String cond) {
        ArrayList a = new ArrayList();
        String[] t = cond.trim().split(" ");
        for (int i = 0; i < this.syntax.length; i++) {
            boolean flag = false;
            if (existsSyntax(t[0], syntax[i])) {
                for (int j = 0; j < t[0].length(); j++) {
                    if (t[0].charAt(j) == this.syntax[i].charAt(0)) {
                        int len = 1;
                        if (this.syntax[i].length() == 2 && t[0].charAt(j + 1) == this.syntax[i].charAt(1)) {
                            len = 2;
                        }
                        //left
                        a.add(t[0].substring(0, j).trim());
                        //syntax
                        a.add(t[0].substring(j, j + len));
                        //right
                        a.add(t[0].substring(j + len, t[0].length()).trim());

                        flag = true;
                        break;
                    }
                }
                if (flag) {
                    break;
                }
            }
        }
        //type left_right: Column_Value - Column_Column - Value_Value
        if (this.isValue((String) a.get(0)) && this.isValue((String) a.get(2))) {
            a.add("Value_Value");
            String s = (((String) a.get(0)).trim());
            if (isChain(s)) {
                s = s.substring(1, s.length() - 1);
                a.set(0, s);
            }
            s = (((String) a.get(2)).trim());
            if (isChain(s)) {
                s = s.substring(1, s.length() - 1);
                a.set(2, s);
            }
        } else if (!this.isValue((String) a.get(0)) && this.isValue((String) a.get(2))) {
            a.add("Column_Value");
            String s = (((String) a.get(2)).trim());
            if (isChain(s)) {
                s = s.substring(1, s.length() - 1);
                a.set(2, s);
            }
        } else {
            a.add("Column_Column");
        }

        //next
        if (t.length == 2) {
            a.add(t[1]);
        } else {
            a.add("");
        }

        return a;
    }

    /**
     * Split a chain of condition into a list condition
     *
     * @param source
     * @param startPos
     * @return a list of condition
     */
    private ArrayList detectCondition(String source, int startPos) {
        int sLen = source.length();
        ArrayList a = new ArrayList();
        for (int i = startPos; i < sLen; i++) {
            //compare to all aperators
            for (int j = 0; j < this.operator.length; j++) {
                int oLen = this.operator[j].length();
                if (i < sLen - 1 - oLen) {
                    String temp = source.substring(i, i + oLen);
                    //if equal
                    if (temp.equals(this.operator[j])) {
                        String cond = source.substring(startPos, i + oLen).trim();
                        source = source.substring(i + oLen, sLen).trim();
                        a.add(cond);

                        i = 0;
                        sLen = source.length();
                        break;
                    }
                }
            }
        }
        a.add(source);

        return a;
    }
    
    private boolean existsSyntax(String s, String syntax){
        int lenSyntax = syntax.length();
        //detect & get syntax
        int len = s.length();
        String syn = null;
        for(int i = 0; i < len - 1; i ++){
            if(s.charAt(i) == '=' || s.charAt(i) == '>' || 
                    s.charAt(i) == '<' || s.charAt(i) == '!'){
                if(s.charAt(i + 1) == '=' || s.charAt(i + 1) == '<' ||
                        s.charAt(i + 1) == '>'){
                    syn = s.substring(i, i + 2);
                    break;
                }
                syn = s.substring(i, i + 1);
                break;
            }
        }
        //check equal
        if(syn != null){
            for(int i = 0; i < lenSyntax; i++){
                if(syn.equals(syntax)){
                    return true;
                }
            }
        }
        
        return false;
    }

    /**
     * Check if a string is value
     *
     * @param s
     * @return true if it's a chain or a number, if not, return false.
     */
    private boolean isValue(String s) {
        //if not a chain - 'asdad'
        if (!isChain(s)) {
            //if not a number - 2324
            for (int i = 0; i < s.length(); i++) {
                if (s.charAt(i) < '0' || s.charAt(i) > '9') {
                    return false;
                }
            }
        }

        return true;
    }

    /**
     * Check if a string is string
     *
     * @param s
     * @return
     */
    private boolean isChain(String s) {
        if (!s.startsWith("'") || !s.endsWith("'")) {
            return false;
        }
        return true;
    }

    /**
     * Save XML document to file
     *
     * @param doc
     */
    public void saveToFile(Document doc, String name) {
        try {
            TransformerFactory transformerFactory = TransformerFactory.newInstance();
            Transformer transformer = transformerFactory.newTransformer();
            DOMSource source = new DOMSource(doc);
            StreamResult result = new StreamResult(new File("D:\\" + name + ".xml"));

            transformer.transform(source, result);

        } catch (TransformerException tfe) {
            JOptionPane.showMessageDialog(null, tfe);
        }
    }

    /**
     * @return the input
     */
    public String getInput() {
        return input;
    }

    /**
     * @param input the input to set
     */
    public void setInput(String input) {
        KeyWord kw = new KeyWord(input);
        this.input = kw.changKeyWordToLowerCase();
    }
}
